package com.zto.sch.advance.excel;

import com.alibaba.excel.EasyExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.stereotype.Service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @program: sch-diy-parent
 * @description:
 * @author: sch
 * @create: 2021-07-29 22:19
 **/
@Service
@Slf4j
public class ExcelService {
    public void writeFileEasyExcel(Integer count) {
        // 写法1
        String fileName = "/Users/sch/mydocument/temp/excel/easyexcel/" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, ExportVo.class).sheet("easyExcel生成").doWrite(data(count));


    }

    public void save(List<ExportVo> list) {

    }

    public void readFileEasyExcel(String originalFilename) {
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet
        EasyExcel.read("/Users/sch/mydocument/temp/excel/easyexcel/" + originalFilename, ExportVo.class, new DemoDataListener()).sheet()
                // 这里可以设置1，因为头就是一行。如果多行头，可以设置其他值。不传入也可以，因为默认会根据DemoData 来解析，他没有指定头，也就是默认1行
                .headRowNumber(1).doRead();
    }

    private List<ExportVo> data(Integer count) {
        List<ExportVo> list = new ArrayList<ExportVo>();
        Date birthday = DateUtils.addYears(new Date(), 30);
        long mobile = 1322000001L;
        for (int i = 0; i < count; i++) {
            ExportVo exportVo = ExportVo.builder().storeId(1405).name("小强" + i).mobile(String.valueOf(mobile + i)).openId("")
                    .appid("").miniOpeId("").sex(i % 2 == 0 ? "男" : "女").birthday(birthday).idCard("").email("123@qq.com")
                    .address("上海市松江区九亭镇九亭大街" + i + "号").education("本科").vocation("金融").hobby("篮球")
                    .profession("会计").income(300000).customerTag("勤劳").build();
            list.add(exportVo);
        }
        return list;
    }

    public int readFilePOI(Sheet sheet) {
        List<ExportVo> exportVos = new ArrayList<>();
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            Cell cell0 = row.getCell(0);
            Cell cell1 = row.getCell(1);
            Cell cell2 = row.getCell(2);
            Cell cell3 = row.getCell(3);
            Cell cell4 = row.getCell(4);
            Cell cell5 = row.getCell(5);
            Cell cell6 = row.getCell(6);
            Cell cell7 = row.getCell(7);
            Cell cell8 = row.getCell(8);
            Cell cell9 = row.getCell(9);
            Cell cell10 = row.getCell(10);
            Cell cell11 = row.getCell(11);
            Cell cell12 = row.getCell(12);
            Cell cell13 = row.getCell(13);
            Cell cell14 = row.getCell(14);
            Cell cell15 = row.getCell(15);
            Cell cell16 = row.getCell(16);
            String storeIdStr = getCellValue(cell0, true);
            String dateStr = getCellValue(cell7, true);
            String incomeStr = getCellValue(cell15, true);
            Integer storeId = StringUtils.isNumeric(storeIdStr) ? Integer.valueOf(storeIdStr) : StringUtils.isEmpty(storeIdStr) ? null : -1;
            Date birthday = null;
            try {
                birthday = StringUtils.isEmpty(dateStr) ? null : DateUtils.parseDate(dateStr, "yyyy/MM/dd");
            } catch (ParseException p3) {
            }
            Integer income = StringUtils.isNumeric(incomeStr) ? Integer.valueOf(incomeStr) : StringUtils.isEmpty(incomeStr) ? null : -1;

            ExportVo exportVo = ExportVo.builder().storeId(storeId).name(getCellValue(cell1, true)).mobile(getCellValue(cell2, true)).openId(getCellValue(cell3, true))
                    .appid(getCellValue(cell4, true)).miniOpeId(getCellValue(cell5, true)).sex(getCellValue(cell6, true)).birthday(birthday).idCard(getCellValue(cell8, true)).email(getCellValue(cell9, true))
                    .address(getCellValue(cell10, true)).education(getCellValue(cell11, true)).vocation(getCellValue(cell12, true)).hobby(getCellValue(cell13, true))
                    .profession(getCellValue(cell14, true)).income(income).customerTag(getCellValue(cell16, true)).build();
            exportVos.add(exportVo);
        }

        return exportVos.size();

    }


    public static String getCellValue(Cell cell, boolean notDecimals) {
        if (cell != null) {
            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC: // 数字
                    short format = cell.getCellStyle().getDataFormat();
                    SimpleDateFormat sdf = null;
                    if (format == 14 || format == 31 || format == 57 || format == 58
                            || (176 <= format && format <= 178) || (182 <= format && format <= 196)
                            || (210 <= format && format <= 213) || (208 == format)) { // 日期
                        sdf = new SimpleDateFormat("yyyy/MM/dd");
                    } else { // 不是日期格式
                        if (notDecimals) {
                            return String.valueOf(Double.valueOf(cell.getNumericCellValue()).longValue());
                        }
                        return String.valueOf(cell.getNumericCellValue());
                    }
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    if (date == null) {
                        return "";
                    }
                    String result = "";
                    try {
                        result = sdf.format(date);
                    } catch (Exception e) {
                        log.info("error", e);
                        return "";
                    }
                    return result;
                case XSSFCell.CELL_TYPE_STRING: // 字符串
                    String cellValue = cell.getStringCellValue();
                    if (StringUtils.isBlank(cellValue)) {
                        return null;
                    }
                    return cellValue.trim();
                case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    return String.valueOf(cell.getBooleanCellValue());
                case XSSFCell.CELL_TYPE_FORMULA: // 公式
                    return String.valueOf(cell.getCellFormula());
                case XSSFCell.CELL_TYPE_BLANK: // 空值
                    return null;
                case XSSFCell.CELL_TYPE_ERROR: // 故障
                    return null;
                default:
                    return null;
            }
        } else {
            return null;
        }
    }
}
